A database is an organized collection of data that can be readily searched, sorted and updated. The database is often logically arranged as tables identified by table names, with columns identified by column names. Access to the database is typically managed by a software interface known as a database management system (DBMS)
In a distributed computing environment, an application running on a first computer may require access to a database residing on a second computer. The computers each include some form of memory and a processor for the storage and execution of instructions, but they are also interconnected through communications equipment to collectively form a computer network so that they may share information.
A three-tier model for database access is a common computer network architecture. As the name implies, the three-tier model includes three layers of processing, namely (1) a client machine, (2) a server machine and (3) a database server. A person, using a client application program that needs to access a database, will be operating the client machine. For example, the client application may be an Internet browser and the database may be a directory of an Internet search engine. The client machine communicates a request to the server machine. The request typically includes a uniform resource locator (URL) that identifies the server machine, the communications protocol, and the desired resource. The server machine determines the location of the database and establishes a connection to the database server, to include any handshaking necessary to secure the connection. Thereafter, the server machine relays the request to the database server. The request is managed by a DBMS also located on the database server. The requested data is transmitted from the database server to the server machine, and it is thereafter relayed to the client machine where it is used by the client application program.
Structured query language (SQL) is a standard protocol for accessing databases. SQL can be used to formulate a database interface statement for subsequently executing a database transaction. SQL includes features that allow users to query, update, insert data into, and delete data from, a database. Requests to a DBMS are made in the form of an SQL transaction.
Java.TM. is an object-oriented programming language developed by Sun Microsystems, Inc. The developers of Java.TM. intended it to be a platform-neutral language. That is, they intended for Java.TM. programs to be developed on any first computer, and then executed on any second computer, independent of the operating systems controlling either of the computers.
Java.TM. programs can be written as stand-alone applications or as applets. An applet is a Java.TM. program, embedded within an Internet web page, and designed to run on a Java.TM.-enabled Internet browser.
As an object-oriented programming language, Java.TM. includes the constructs of class, object, member variables and methods. A class is a template for defining the characteristics of a specific set of data structures and routines. The variables and routines defined by the class are respectively known as member variables and methods. When a program wishes to use those specific data structures and routines, the program instantiates the class, thus creating an object of the class. Additionally, Java.TM. includes a vector class that handles arrays of variable size for which memory can be allocated as the need arises.
In the case where a Java.TM. program requires access to a database, the program can employ the JDBC.TM. Application Program Interface (JDBC.TM. API) and its associated JDBC.TM. drivers. "JDBC" may have, at one time, been an acronym for Java.TM. Database Connectivity, but it is now a trademark of Sun Microsystems, Inc. Through JDBC.TM., programs written in Java.TM. may access many popular databases using standard SQL protocol. More specifically, a Java- program can use JDBC.TM. to establish a connection with a database, send SQL statements to the database, process a result from the database and return a result to the Java.TM. program.
JDBC.TM. includes the capability of representing a PreparedStatement object. A PreparedStatement is an object used for SQL statements that take one or more parameters as input arguments. It is pre-compiled and stored for future, repetitive use.
FIG. 1 illustrates a technique used in the prior art to access a database from a client application in a network organized as a three-tier model. The system includes client machine 10, server machine 50 and database server 20. Assume client application 12 desires access to database 24. Client machine 10 communicates its request to server machine 50, wherein task 60 is created. Task 60 invokes internal subtasks 62, which individually formulate SQL statements and reformat the SQL statements according to JDBC.TM. protocol. JDBC.TM. 70 is comprised of JDBC.TM. API 72 and JDBC.TM. driver 74. Each of subtasks 62 individually interface with JDBC.TM. 70 via JDBC.TM. API 72. In turn, for each subtask 62, JDBC.TM. driver 74 establishes a connection to database 24 via DBMS 22. When JDBC.TM. 70 receives a result from database 24, it processes and relays the result to the appropriate subtask 62. Thereafter, each subtask 62 closes its individual database connection.
The prior art suffers several drawbacks. Typically, each of the various subtasks 62 will support different types of database accesses such as query only, insert only, query and update, or query and delete. Some of subtasks 62 may require a single database access while others may require multiple accesses. An individual subtask 62 opens and closes database connections without regard to other subtasks 62 that may be accessing the database at the same time. Consequently, task 60 may have several database connections open concurrently. This is inefficient and may degrade performance of the application as a whole. In addition, since each of subtasks 62 independently interface with JDBC.TM. 70, the interfaces are redundant. This redundancy can lead to an increase in the number of defects and increased maintenance costs if the JDBC.TM. interface changes.
Accordingly, a first object of the present invention is to provide an object-oriented class that can be instantiated by a task to establish and retain a single database connection until the task is completed, thus avoiding multiple and concurrent connections to the database.
A second object of the present invention is to provide an object-oriented class bundling all database functions such as connect, query, update, and delete, and further defining methods by which a task can easily and efficiently interface with an application program interface to access a database, wherein a programmer developing the task need not be familiar with either SQL or the details of the application program interface.
A third object of the present invention is to provide an object-oriented class defining methods where database queries are constructed in advance, such as with JDBC.TM. PreparedStatement objects, and then employed to expedite repetitive SQL transactions.